package com.wzy.Jdbc封装.dao;

import com.wzy.Jdbc封装.config.ConfigManage;
import com.wzy.Jdbc封装.pojo.*;
import com.wzy.level.byPage.byPageDto.ByPage;

import java.sql.*;

public class NewsDao {
    //数据库连接需要用到的一些对象
    static Connection connection = null;
    static PreparedStatement preparedStatement = null;
    static ResultSet resultSet = null;

    //读取外部数据库的配置信息
    static  final String PATH = "D:\\web_project\\src\\main\\resources\\database.properties";
    static  final  String DRIVER = ConfigManage.getConfig(PATH).getValue("driver");
    static  final  String URL = ConfigManage.getConfig(PATH).getValue("url");
    static  final  String USER = ConfigManage.getConfig(PATH).getValue("user");
    static  final  String PASS = ConfigManage.getConfig(PATH).getValue("password");

//    //增 删 改  返回操作成功行数
//    public static int update(String sql, Object[] obj) {
//        int rows = 0;
//        try {
//            connection = NewsDao.getConnection();
//            preparedStatement = connection.prepareStatement(sql);
//            if (obj != null) {
//                for (int i = 0; i < obj.length; i++) {
//                    preparedStatement.setObject(i + 1, obj[i]);
//                }
//                rows = preparedStatement.executeUpdate();
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            Connection connection = null;
//            PreparedStatement preparedStatement = null;
//            ResultSet resultSet = null;
//            NewsDao.closeConnection(resultSet, preparedStatement, connection);
//        }
//        return rows;
//    }
    //News_category表
    //增 返回操作成功行数
    public static int insert(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                News_category news = (News_category) obj[0];
                    //news_category表
                    preparedStatement.setObject( 1,news.getId());
                    preparedStatement.setObject(2, news.getName());
                    preparedStatement.setObject(3, news.getDate());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    public static int insert0(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                //news_category表
                if (obj != null && obj.length>0) {
                    for (int i = 0; i<obj.length; ++i){
                        preparedStatement.setObject( i+1,obj[i]);
                    }
                    rows = preparedStatement.executeUpdate();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //删 返回操作成功行数
    public static int delete(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
//                News_category news = (News_category) obj[0];
                //news_category表
//                preparedStatement.setObject( 1,news.getId());
                for (int i = 0; i<obj.length; ++i){
                    preparedStatement.setObject( i+1,obj[i]);
                }
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
//    改 返回操作成功行数
    public static int update(String sql, Object[] obj) {
    int rows = 0;
    try {
        connection = NewsDao.getConnection();
        preparedStatement = connection.prepareStatement(sql);
        if (obj != null) {
            News_category news = (News_category) obj[0];
            //news_category表
            preparedStatement.setObject( 3,news.getId());
            preparedStatement.setObject( 2,news.getDate());
            preparedStatement.setObject(1, news.getName());
            rows = preparedStatement.executeUpdate();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        NewsDao.closeConnection(resultSet, preparedStatement, connection);
    }
    return rows;
}
    //根据id查询所有信息  返回结果集
    public static ResultSet selectById(String sql, Object[] obj) {
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null && obj.length>0) {
                News_category category = (News_category) obj[0];
                    preparedStatement.setObject(1,category.getId());
                resultSet = preparedStatement.executeQuery();
            } else {
                resultSet = preparedStatement.executeQuery();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static ResultSet selectById0(String sql, Object[] obj) {
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null && obj.length>0) {
                for (int i = 0; i<obj.length; ++i){
                    preparedStatement.setObject( i+1,obj[i]);
                }
                resultSet = preparedStatement.executeQuery();
            } else {
                resultSet = preparedStatement.executeQuery();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    //News_comment表
    //增 返回操作成功行数
    public static int insert1(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                News_comment news = (News_comment) obj[0];
                //news_category表
                preparedStatement.setObject( 1,news.getId());
                preparedStatement.setObject( 2,news.getNewsId());
                preparedStatement.setObject(3, news.getContent());
                preparedStatement.setObject(4, news.getAuthor());
                preparedStatement.setObject(5, news.getIp());
                preparedStatement.setObject(6, news.getDate());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //删 返回操作成功行数
    public static int delete1(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                News_comment news = (News_comment) obj[0];
                //news_category表
                preparedStatement.setObject( 1,news.getId());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //    改 返回操作成功行数
    public static int update1(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                News_comment news = (News_comment) obj[0];
                //news_category表
                preparedStatement.setObject( 2,news.getId());
                preparedStatement.setObject(1, news.getAuthor());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    public static int update11(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                News_comment news = (News_comment) obj[0];
                preparedStatement.setObject(1, news.getNewsId());
                preparedStatement.setObject(2, news.getContent());
                preparedStatement.setObject(3, news.getAuthor());
                preparedStatement.setObject(4, news.getIp());
                preparedStatement.setObject(5, news.getDate());
                preparedStatement.setObject( 6,news.getId());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //根据id查询所有信息  返回结果集
    public static ResultSet selectById1(String sql, Object[] obj) {
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null && obj.length>0) {
                News_comment comment = (News_comment) obj[0];
                preparedStatement.setObject(1,comment.getId());
                resultSet = preparedStatement.executeQuery();
            } else {
                resultSet = preparedStatement.executeQuery();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static ResultSet selectById11(String sql, Object[] obj) {
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null && obj.length>0) {
                for (int i = 0; i<obj.length; ++i){
                    preparedStatement.setObject( i+1,obj[i]);
                }
                resultSet = preparedStatement.executeQuery();
            } else {
                resultSet = preparedStatement.executeQuery();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    //New_detail
    public static int insert2(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                News_detail news = (News_detail) obj[0];
                //news_category表
                preparedStatement.setObject( 1,news.getId());
                preparedStatement.setObject(2, news.getCategoryId());
                preparedStatement.setObject(3, news.getTitle());
                preparedStatement.setObject(4, news.getSummary());
                preparedStatement.setObject(5, news.getContent());
                preparedStatement.setObject(6, news.getPicPath());
                preparedStatement.setObject(7, news.getAuthor());
                preparedStatement.setObject(8, news.getDate());
                preparedStatement.setObject(9, news.getDate1());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //删 返回操作成功行数
    public static int delete2(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                News_detail news = (News_detail) obj[0];
                //news_category表
                preparedStatement.setObject( 1,news.getId());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //    改 返回操作成功行数
    public static int update2(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                News_detail news = (News_detail) obj[0];
                //news_category表
                preparedStatement.setObject( 2,news.getId());
                preparedStatement.setObject(1, news.getTitle());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    public static int update22(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                News_detail news = (News_detail) obj[0];

                preparedStatement.setObject( 1,news.getCategoryId());
                preparedStatement.setObject( 2, news.getTitle());
                preparedStatement.setObject( 3,news.getSummary());
                preparedStatement.setObject( 4,news.getContent());
                preparedStatement.setObject( 5,news.getPicPath());
                preparedStatement.setObject( 6,news.getAuthor());
                preparedStatement.setObject( 7,news.getDate());
                preparedStatement.setObject( 8,news.getDate1());
                preparedStatement.setObject( 9,news.getId());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //根据id查询所有信息  返回结果集
    public static ResultSet selectById2(String sql, Object[] obj) {
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null && obj.length>0) {
                News_detail detail = (News_detail) obj[0];
                preparedStatement.setObject(1,detail.getId());
                resultSet = preparedStatement.executeQuery();
            } else {
                resultSet = preparedStatement.executeQuery();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static ResultSet selectById22(String sql, Object[] obj) {
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null && obj.length>0) {
                for (int i = 0; i<obj.length; ++i){
                    preparedStatement.setObject( i+1,obj[i]);
                }
                resultSet = preparedStatement.executeQuery();
            } else {
                resultSet = preparedStatement.executeQuery();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static ResultSet selectByPage(String sql, Object[] obj) {
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            ByPage byPage = null;
            if (obj != null && obj.length>0) {
               byPage =(ByPage)obj[0];
                    preparedStatement.setObject( 1,byPage.getIndex());
                    preparedStatement.setObject( 2,byPage.getLimit());

                resultSet = preparedStatement.executeQuery();
            } else {
                resultSet = preparedStatement.executeQuery();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    //News_user表
    public static int insert3(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                News_user news = (News_user) obj[0];
                //news_category表
                preparedStatement.setObject( 1,news.getId());
                preparedStatement.setObject(2, news.getUserName());
                preparedStatement.setObject(3, news.getPassword());
                preparedStatement.setObject(4, news.getEmail());
                preparedStatement.setObject(5, news.getUserType());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //删 返回操作成功行数
    public static int delete3(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                News_user news = (News_user) obj[0];
                //news_category表
                preparedStatement.setObject( 1,news.getId());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //    改 返回操作成功行数
    public static int update3(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                News_user news = (News_user) obj[0];
                //news_user表
                preparedStatement.setObject(1, news.getUserName());
                preparedStatement.setObject(2, news.getPassword());
                preparedStatement.setObject(3, news.getEmail());
                preparedStatement.setObject(4, news.getUserType());
                preparedStatement.setObject( 5,news.getId());

                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            NewsDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //根据id查询所有信息  返回结果集
    public static ResultSet selectById3(String sql, Object[] obj) {
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null && obj.length>0) {
                News_user user = (News_user) obj[0];
                preparedStatement.setObject(1,user.getId());
                resultSet = preparedStatement.executeQuery();
            } else {
                resultSet = preparedStatement.executeQuery();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }


    public static ResultSet selectById31(String sql, Object[] obj) {
        try {
            connection = NewsDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null && obj.length>0) {
                for (int i = 0; i<obj.length; ++i){
                    preparedStatement.setObject( i+1,obj[i]);
                }
                resultSet = preparedStatement.executeQuery();
            } else {
                resultSet = preparedStatement.executeQuery();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }




    //连接数据库驱动 返回驱动管理的对象
    public static Connection getConnection() {
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USER, PASS);
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
        return connection;
    }
//    public static Connection getConnection(){
//        try {
//            Context context = new InitialContext();
//            String jndi = "java:comp/env/jdbc/news";
//            DataSource source = (DataSource) context.lookup(jndi);
//            connection = source.getConnection();
//        }catch (Exception e){
//            e.printStackTrace();
//        }
//        return connection;
//    }

    //关闭连接数据库所需要的所有对象
    public static void closeConnection(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
